﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class NoticeGateway:DBGateway
    {
        public bool PublishNotice(Notice notice)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblNotice values(@date,@subject,@summary,@image,@noticeBy)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = notice.Date;
                CommandObj.Parameters.Add("@subject", SqlDbType.NVarChar).Value = notice.Subject;
                CommandObj.Parameters.Add("@summary", SqlDbType.NVarChar).Value = notice.Summary;
                CommandObj.Parameters.Add("@image", SqlDbType.NVarChar).Value = notice.NoticeImage;
                CommandObj.Parameters.Add("@noticeBy", SqlDbType.NVarChar).Value = notice.NoticeBy;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Publishing Notice");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public bool UpdateNotice(Notice notice)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblNotice set Date=@date,Subject=@subject,Summary=@summary,NoticeImage=@image,NoticeBy=@noticeBy where Id='"+notice.Id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = notice.Date;
                CommandObj.Parameters.Add("@subject", SqlDbType.NVarChar).Value = notice.Subject;
                CommandObj.Parameters.Add("@summary", SqlDbType.NVarChar).Value = notice.Summary;
                CommandObj.Parameters.Add("@image", SqlDbType.NVarChar).Value = notice.NoticeImage;
                CommandObj.Parameters.Add("@noticeBy", SqlDbType.NVarChar).Value = notice.NoticeBy;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Updateing Notice");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public bool DeleteNotice(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Delete from tblNotice where Id='" +id + "'");
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Deleting Notice");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false; 
        }

        public List<Notice> GetAllNotices()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblNotice order by Date DESC";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Notice>notices=new List<Notice>();
                while (reader.Read())
                {
                    Notice notice=new Notice();
                    notice.Id =(int)reader[0];
                    notice.Date = Convert.ToDateTime(reader[1]).ToShortDateString();
                    notice.Subject = reader[2].ToString();
                    notice.Summary = reader[3].ToString();
                    notice.NoticeImage = reader[4].ToString();
                    notice.NoticeBy = reader[5].ToString();
                    notices.Add(notice);
                }
                return notices;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Retriving Notices");
            }
            finally
            {
                ConnectionObj.Close();
            }
           
        }

        public Notice GetNotice(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblNotice where Id='" + id + "' order by Date DESC";
                SqlDataReader reader = CommandObj.ExecuteReader();
                Notice notice = new Notice();
                while (reader.Read())
                {
                    notice.Id = (int)reader[0];
                    notice.Date = Convert.ToDateTime(reader[1]).ToShortDateString();
                    notice.Subject = reader[2].ToString();
                    notice.Summary = reader[3].ToString();
                    notice.NoticeImage = reader[4].ToString();
                    notice.NoticeBy = reader[5].ToString();
                 }
                return notice;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Retriving Notices");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}